#   根据master文件，与slave文件的每行相比较，根据nan数据的多少，找出需要补全的行
#   要求两个文件行数相等, 要求顺序相同
#   不要求标题行在第一行，要求标题行在同一行，要求标题行第一列为‘序号’
#   将多个slave文件向master文件整合 , 对于某一slave文件读取失败不会影响其他文件合并
#   支持多sheet合并, 仅限有序号标题的合并

from ExcelClass import Sheet
import pandas


def combine_all_in_one(path, master_filename, *arg):
    for filename in arg:
        sheets = pandas.ExcelFile(path + filename).sheet_names
        for sheet in sheets:
            print(f'slave file name: {filename}, sheet name: {sheet}')
            combine_two_in_one(path, master_filename, filename, sheet)


def combine_two_in_one(path, master_filename, slave_filename, sheet='Sheet2'):

    try:
        slave_excel = Sheet(path, slave_filename, sheet)
        master_excel = Sheet(path, master_filename, sheet)
        print(f'{slave_filename}读取成功')
    except :
        print(f'{slave_filename}读取失败')
        # 从文件读取失败, 直接return, 读取下一个从文件
        return

    # get master file's row num
    master_row_num = master_excel.get_col_num()
    print('row_num:', master_excel.get_col_num())
    # traverse each row and compare nan values
    key_line_num = 1  # 标题行所在行数
    try:
        for i, row in enumerate(range(master_row_num)):
            master_line_content = master_excel.get_line(row)
            slave_line_content = slave_excel.get_line(row)
            # print(f'i:{i}, {master_excel.get_nan_count_by_line(i)} <{slave_excel.get_nan_count_by_line(i)}' )

            if master_excel.get_nan_count_by_line(i) > slave_excel.get_nan_count_by_line(i):
                # 如果主文件当前行的空白单元格相比更多
                # print(f'need to recover on serial: {master_excel.get_serial_nums()}')
                # print(f'need to recover on serial: {master_line_content[0]},{master_line_content[2]}')
                # 将符合条件从文件的行覆盖主文件， i+1是为了跳过标题行
                master_excel.cover_line(i + key_line_num, slave_line_content)

        print(f'{slave_filename}合并成功')
    except Exception as e:
        print(e)


if __name__ == '__main__':
    file_path = './data_source/'
    master = 'panda_read_excel.xlsx'
    slave1 = 'panda_read_excel--1.xlsx'
    slave2 = 'panda_read_excel--2.xlsx'
    combine_all_in_one(file_path, master, slave1, slave2)
